package com.no1.musicmgr.mapper;

import com.no1.musicmgr.vo.MusicVo;
import com.no1.musicmgr.vo.UserVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;

/**
 * @author 17739
 */
@Mapper
public interface MusicAnalysisMapper  {
    @Select("SELECT MONTH(music_time) AS month, COUNT(*) AS musicActive FROM music GROUP BY MONTH(music_time)")
    ArrayList<MusicVo> getMusicCountPerMonth();

    @Select("select music_type,count(*) as count from music group by music_type")
    ArrayList<MusicVo> getMusicDataByType();

    @Select("select music_level,count(*) as count from music group by music_level")
    ArrayList<MusicVo> getMusicDataByLevel();

    @Select("select music_rm,count(*) as count from music group by music_rm")
    ArrayList<MusicVo> getMusicDataByRm();

    @Select("SELECT COUNT(*) AS totalMusicCount FROM music")
    ArrayList<MusicVo> getTotalMusicCount();

    @Select("SELECT COUNT(*) AS totalMusicRm FROM music WHERE music_rm = 1")
    ArrayList<MusicVo> getTotalMusicRm();

    @Select("SELECT COUNT(*) AS musicFreeCount FROM music WHERE music_level = 2")
    ArrayList<MusicVo> getMusicFreeCount();

    @Select("SELECT COUNT(*) AS releaseThisYear FROM music WHERE YEAR(music_time) = YEAR(CURRENT_DATE)")
    ArrayList<MusicVo> getReleaseThisYear();

    @Select("SELECT COUNT(*) AS releaseLastYear FROM music WHERE YEAR(music_time) = YEAR(CURRENT_DATE) - 1")
    ArrayList<MusicVo> getReleaseLastYear();

    @Select("SELECT COUNT(*) AS totalTuCount FROM music WHERE music_tu IS NOT NULL")
    Integer getTotalCountForTu();

    @Select("SELECT SUM(IFNULL(music_tu, 0)) AS totalTu FROM music")
    Integer getTotalTu();

    @Select("SELECT COUNT(*) AS totalScCount FROM music WHERE music_sc IS NOT NULL")
    Integer getTotalCountForSc();

    @Select("SELECT SUM(IFNULL(music_sc, 0)) AS totalSc FROM music")
    Integer getTotalSc();

}
